package vn.com.demand.lux.criteria;

import java.sql.Date;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import vn.com.demand.lux.model.WarrantyReservalCaculationlBean;
import vn.com.demand.util.DpUtils;

public class WarrantyAccrualCaculationCriteria {

	private Date fromDate;
	private Date toDate;
	private String expenseLedger;
	
	@SuppressWarnings("unchecked")
	public List<WarrantyReservalCaculationlBean> search() throws SQLException {
		String sql = "SELECT TOP 100 PERCENT ContractNumber, MAX(Type) AS Type, MAX(CostCenter) AS CostCenter, SUM(NetSales) AS NetSales, " +
			  " SUM(AccumulatedWarrantyExpense) AS AccumulatedWarrantyExpense, SUM(WarrantyExpense) AS WarrantyExpense" +
			  " FROM (SELECT g.project AS ContractNumber, p.CostCenter, k.oms25_0 AS Type, CASE WHEN LEFT(ltrim(g.reknr), 3) = '511' " +
			  " THEN - g.bdr_hfl ELSE 0 END AS NetSales, CASE WHEN Ltrim(ltrim(g.reknr)) = '_expenseLedgerParam' AND g.Selcode IS NOT NULL " +
			  " AND (p.InitialStartDate BETWEEN CONVERT(DATETIME, '_fromDateParam', 102) AND CONVERT(DATETIME, '_toDateParam', 102)) " +
			  " THEN g.bdr_hfl ELSE 0 END AS AccumulatedWarrantyExpense, CASE WHEN Ltrim(ltrim(g.reknr)) = '_expenseLedgerParam' " +
			  " AND g.Selcode IS NULL THEN g.bdr_hfl ELSE 0 END AS WarrantyExpense FROM dbo.gbkmut g " +
			  " INNER JOIN dbo.PRProject p ON g.project = p.ProjectNr LEFT OUTER JOIN dbo.kstdr k ON p.Costunit = k.kstdrcode" +
			  " WHERE (p.YesNoField1 + YesNoField2 > 0) AND (p.Type IN ('I')) AND (NOT (g.transtype IN ('V', 'B')))) g" +
			  " GROUP BY ContractNumber HAVING SUM(AccumulatedWarrantyExpense) > 0 AND SUM(WarrantyExpense) > 0" +
			  " ORDER BY ContractNumber";
		sql = sql.replaceAll("_expenseLedgerParam", expenseLedger);
		sql = sql.replaceAll("_fromDateParam", DpUtils.format(fromDate, "yyyy-MM-dd"));
		sql = sql.replaceAll("_toDateParam", DpUtils.format(toDate, "yyyy-MM-dd"));
//		System.out.println("SQL:" +sql);
		QueryRunner runner = new QueryRunner(DpUtils.getDataSource());
		return (List<WarrantyReservalCaculationlBean>) runner.query(sql, new BeanListHandler(WarrantyReservalCaculationlBean.class));
	}
	
	public Date getFromDate() {
		return fromDate;
	}

	public void setFromDate(Date fromDate) {
		this.fromDate = fromDate;
	}

	public Date getToDate() {
		return toDate;
	}

	public void setToDate(Date toDate) {
		this.toDate = toDate;
	}

	public String getExpenseLedger() {
		return expenseLedger;
	}

	public void setExpenseLedger(String expenseLedger) {
		this.expenseLedger = expenseLedger;
	}

}
